import Router from "koa-router";
import { pool } from "../../config/database";
import { RowDataPacket } from "mysql2";
import { ApiResponse } from "../../types";

const router = new Router({ prefix: "/api/categories" });

// 获取启用的类别列表
router.get("/", async (ctx) => {
  try {
    const { parent_id, include_children = 'false' } = ctx.query;

    let whereClause = "WHERE is_active = 1";
    const params: any[] = [];

    if (parent_id !== undefined) {
      if (parent_id === 'null' || parent_id === null) {
        whereClause += " AND parent_id IS NULL";
      } else {
        whereClause += " AND parent_id = ?";
        params.push(parseInt(parent_id as string));
      }
    }

    const [categories] = await pool.execute(`
      SELECT 
        id, name, slug, description, parent_id, image, icon, sort_order,
        (SELECT COUNT(*) FROM products WHERE category_id = categories.id AND status = 'active') as products_count
      FROM categories
      ${whereClause}
      ORDER BY sort_order ASC, name ASC
    `, params) as [RowDataPacket[], any];

    // 如果需要包含子类别
    if (include_children === 'true') {
      for (let category of categories) {
        const [children] = await pool.execute(`
          SELECT 
            id, name, slug, description, parent_id, image, icon, sort_order,
            (SELECT COUNT(*) FROM products WHERE category_id = categories.id AND status = 'active') as products_count
          FROM categories
          WHERE parent_id = ? AND is_active = 1
          ORDER BY sort_order ASC, name ASC
        `, [category.id]) as [RowDataPacket[], any];
        
        category.children = children;
      }
    }

    ctx.body = {
      success: true,
      data: categories,
      message: "获取类别列表成功"
    } as ApiResponse;

  } catch (error: any) {
    console.error("获取类别列表失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "获取类别列表失败：" + error.message
    } as ApiResponse;
  }
});

// 获取类别树形结构
router.get("/tree", async (ctx) => {
  try {
    // 获取所有启用的类别
    const [categories] = await pool.execute(`
      SELECT 
        id, name, slug, description, parent_id, image, icon, sort_order,
        (SELECT COUNT(*) FROM products WHERE category_id = categories.id AND status = 'active') as products_count
      FROM categories
      WHERE is_active = 1
      ORDER BY sort_order ASC, name ASC
    `) as [RowDataPacket[], any];

    // 构建树形结构
    const buildTree = (items: any[], parentId: number | null = null): any[] => {
      return items
        .filter(item => item.parent_id === parentId)
        .map(item => ({
          ...item,
          children: buildTree(items, item.id)
        }));
    };

    const tree = buildTree(categories);

    ctx.body = {
      success: true,
      data: tree,
      message: "获取类别树形结构成功"
    } as ApiResponse;

  } catch (error: any) {
    console.error("获取类别树形结构失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "获取类别树形结构失败：" + error.message
    } as ApiResponse;
  }
});

// 根据slug获取类别详情
router.get("/:slug", async (ctx) => {
  try {
    const { slug } = ctx.params;

    const [categories] = await pool.execute(`
      SELECT 
        c.*, 
        p.name as parent_name,
        (SELECT COUNT(*) FROM categories WHERE parent_id = c.id AND is_active = 1) as children_count,
        (SELECT COUNT(*) FROM products WHERE category_id = c.id AND status = 'active') as products_count
      FROM categories c
      LEFT JOIN categories p ON c.parent_id = p.id
      WHERE c.slug = ? AND c.is_active = 1
    `, [slug]) as [RowDataPacket[], any];

    if (categories.length === 0) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: "类别不存在"
      } as ApiResponse;
      return;
    }

    // 获取子类别
    const [children] = await pool.execute(`
      SELECT 
        id, name, slug, description, image, icon, sort_order,
        (SELECT COUNT(*) FROM products WHERE category_id = categories.id AND status = 'active') as products_count
      FROM categories
      WHERE parent_id = ? AND is_active = 1
      ORDER BY sort_order ASC, name ASC
    `, [categories[0].id]) as [RowDataPacket[], any];

    categories[0].children = children;

    ctx.body = {
      success: true,
      data: categories[0],
      message: "获取类别详情成功"
    } as ApiResponse;

  } catch (error: any) {
    console.error("获取类别详情失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "获取类别详情失败：" + error.message
    } as ApiResponse;
  }
});

// 获取热门类别
router.get("/popular/list", async (ctx) => {
  try {
    const { limit = 10 } = ctx.query;

    const [categories] = await pool.execute(`
      SELECT 
        c.id, c.name, c.slug, c.description, c.image, c.icon,
        COUNT(p.id) as products_count
      FROM categories c
      LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
      WHERE c.is_active = 1
      GROUP BY c.id, c.name, c.slug, c.description, c.image, c.icon
      HAVING products_count > 0
      ORDER BY products_count DESC, c.sort_order ASC
      LIMIT ?
    `, [parseInt(limit as string)]) as [RowDataPacket[], any];

    ctx.body = {
      success: true,
      data: categories,
      message: "获取热门类别成功"
    } as ApiResponse;

  } catch (error: any) {
    console.error("获取热门类别失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "获取热门类别失败：" + error.message
    } as ApiResponse;
  }
});

export default router; 